/* Author: Matthew G. Monteleone mgm@mgmonteleone.com
Please let me know if you use this code, or think it rocks or sucks, or have any suggestions on how to extend or improve....
 This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.

*/

this.gss = SpreadsheetApp.getActiveSpreadsheet()
 this.serverAddress = ""; // put the public ip address or dns name of your sql server here.
 this.serverPort = "9999"; // the mysql port to use
 this.username = ""; // the mysql user name
 this.pass = ""; // the mysql password
 ScriptProperties.setProperty('databaseName', ''); // set the name of the mysql to be used database here.
 this.theDatabase = ScriptProperties.getProperty('databaseName'); //makes that value avail as a global

function onOpen() { 
  // We Set up menu items here, each menu item can call a stored Procedure specific JDBC Call
  var refreshMenuEntries = [];
  //searchMenuEntries.push({name: "Show Control Panel", functionName:"showapp"});
  refreshMenuEntries.push({name: "Refresh", functionName:"setDates"});    
  //make sure we have a databasename
 gss.addMenu("Refresh Data", refreshMenuEntries);
 
}
/*
Small GAS UI application which is used to pass variables to the stored procedure.
Here as an example of how to set and retrieve variables for a database call.
*/
function setDates(){
  var app = UiApp.createApplication().setTitle("Please select the dates of the calls to list").setHeight(150).setWidth(300);
  var btnOK = app.createButton("Get Data").setEnabled(false).setId("btnOK");
  var boxFrom = app.createTextBox().setName("boxFrom").setId("boxFrom");
  var boxTo = app.createTextBox().setName("boxTo").setId("boxTo");
  var lblFrom = app.createLabel("Date From (yyyy-mm-dd)");
  var lblTo = app.createLabel("Date To (yyyy-mm-dd)")
  var appPanel = app.createVerticalPanel().setId("appPanel");
  appPanel.add(lblFrom);
  appPanel.add(boxFrom);
  appPanel.add(lblTo);
  appPanel.add(boxTo);
  appPanel.add(btnOK);
/*
Since we need dates as text, not as a real date datatype, we need to validate
the dates in the UI.
*/
  var validate = app.createClientHandler().validateLength(boxFrom, 10, 10).forTargets(btnOK).setEnabled(true).forTargets(boxFrom).setStyleAttribute("color", "black");
  var invalidate = app.createClientHandler().validateNotLength(boxFrom, 10, 10).forTargets(btnOK).setEnabled(false).forTargets(boxFrom).setStyleAttribute("color", "red");
  
  var validateTo = app.createClientHandler().validateLength(boxTo, 10, 10).forTargets(btnOK).setEnabled(true).forTargets(boxTo).setStyleAttribute("color", "black");
  var invalidateTo = app.createClientHandler().validateNotLength(boxTo, 10, 10).forTargets(btnOK).setEnabled(false).forTargets(boxTo).setStyleAttribute("color", "red");
  
// Add the handelers for validation.
  var handler = app.createServerClickHandler('getAllNew');
  handler.addCallbackElement(appPanel);
  btnOK.addClickHandler(handler);
  boxFrom.addKeyUpHandler(validate);
  boxFrom.addKeyUpHandler(invalidate);
  boxTo.addKeyUpHandler(validateTo);
  boxTo.addKeyUpHandler(invalidateTo);

  app.add(appPanel);
  gss.show(app);


}

// Here we create specific functions which call specific store procedures using the core functions.
/*
The example here is for a stored procedure which pulls call recordings from 
an Elastix/Queue stats system, and pulls back urls to allow call listening from
GAPPS. But, of course you can use any SP you want.
*/

function getAllNew(e) {
  SpreadsheetApp.getActiveSpreadsheet().toast("Getting Recordings for period:\n"+e.parameter.boxFrom+" to "+e.parameter.boxTo);
/*
Here we create an arry for sending variables to our stored procedure. Since this
script works dynamically, you can add any number of parameters to the array, and
they will be passed to the SP.

Keep in mind, text and date values must be single quoted in your array.
*/
  
  var theKeys = new Array();
  
  /*Here we push the values from our GAS UI app into the parameter array.
  Keep in mind that you need to push the values in reverse orded, in other words
  you need to put the last parameter to be sent to the SP in first.
  */
theKeys.push("'"+e.parameter.boxFrom+"'");
theKeys.push("'"+e.parameter.boxTo+"'");
/* Now we call the putData function, which gets the data from the database, and
puts the returned data into the specified sheet(tab).
The parameters to this function are
            ˇ The name of the sheet to put the returned data.
            			ˇ The name of the MySQL sp to call
            						ˇ The name of the array
            						to be sent.
*/
putData("Recordings","inbound_get_recs_all_by_dates",theKeys);
 
  app.close();
  // The following line is REQUIRED for the widget to actually close.
  return app;
}


/* This function gets data by calling getDataToArrays, accepting the tabname we
want to place the data (if this sheet does not yet exist, it will be created)
, the sp to be called, and an array with the variables(keys) to be passed to the SP.
To create the array to be sent to this function, do something like this:

var theKeys = new Array();
theKeys.push("'2012-01-01'");
theKeys.push("'2012-01-31'");
theKeys.push(10);
!! For varchar(text) and date variable types, it is important to do the quoting,
using single quotes, in the array that you create.

*/

function putData(tabName,SPName,theKeys){
  var dataBaseName = ScriptProperties.getProperty('databaseName');
  try {
  var doc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabName);//set the sheet to use.
  // if the sheet doesnt exist, create it.
  if (doc == null)
  {SpreadsheetApp.getActiveSpreadsheet().insertSheet(tabName);
  var doc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabName);}
  }
  catch(e) { throw new Error("There was a problem finding or creating the target sheet")
  }
  try{
  	  // We call the data handeling function to return us data and meta-data.
  var theData = getDatatoArrays(serverAddress,serverPort,username,pass,dataBaseName,SPName,theKeys); //get three arrays in an object to use...
  }
  catch(e){throw new Error("Could not get data, perhaps there is a network or database problem?\n"+e.name+"\n"+e.message)}
  doc.getDataRange().clear(); //clear that doc
  var colcount = theData.typeArray.length; //get the length of the typeArray in to set columncount
  // The infoRange shows information about the last refresh, etc. You can remove
  //this part if you dont need this information shown to end users.
  var infoRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabName).getRange(1, 1, 1, colcount);
  var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  var currentDateTime = Utilities.formatDate(new Date(), timeZone, "yyyy-MM-dd' 'HH:mm:ss");
  var headerMsg = tabName+" Osvježen: "+ currentDateTime; // a little bit of Croatian :)
  infoRange.mergeAcross().setBackgroundColor("Khaki").setFontWeight("strong").setValue(headerMsg);
  var headerRange = doc.getRange(2,1,1,colcount); //set the headerarray to the correct size
  headerRange.setValues(theData.labelArray); //set the actual values
  headerRange.setBackgroundColor("Orange"); //formatting
  headerRange.setFontWeight("bold");
      // apply the dataArray 2d array to the sheet.
  var  rowcount = theData.dataArray.length; // find out how many rows we have in the result set.
    doc.getRange(3,1,rowcount,colcount).setValues(theData.dataArray); //now actually apply the rows...
    SpreadsheetApp.getActiveSpreadsheet().toast(tabName+" updated!","The "+tabName+" data has been updated with "+rowcount+" calls.",10)
 }


/*
This function connects to the database,gets the record set, and returns 3 arrays
in an object.
- Is sent:
	- Connection information
	- The procedure to be called and
	- An array with the variables to be passed to the SP. 
In this manner this function can take a dynamic number of variables..
- Gets metadata for the record set to be used for labels, and datatype handeling. 
Puts that data into 2 arrays, typeArray and labelArray.
- Dynamically gets data from stored procedure, and puts that data into a 2D array
for use in applying to a range. Uses meta date to use the best getter to try
to ensure that data format is ensured as well as possible. There seems to be
bugs in some of the getters, so they are commented out, and just text getter is
used.
*/
function getDatatoArrays(serverAddress,serverPort,username,pass,dataBaseName,SPName,theKeys) {
  var connstring = "jdbc:mysql://"+serverAddress+":"+serverPort+"/"+dataBaseName;
  try{
  var conn = Jdbc.getConnection(connstring, username, pass);
  }
  catch(e){{throw new Error("Could not connect to the database.\n"+e.name+"\n"+e.message)}}
  var stmt = conn.createStatement();
  stmt.setMaxRows(7500); // get maximum X rows
  stmt.setQueryTimeout(0); //sets timeout to X secs, 0 is unlimited.
  /* This portion dynamically builds the SP call based on how many variables are sent */
  var numberOfKeys = theKeys.length; //how many params where sent in array?
  Logger.log("Found "+numberOfKeys+" keys in the passed array.");
  if (numberOfKeys == 0){ //if no params passed, then we just call the SP....
    var query = "call "+SPName+"()"
  }
  else if (numberOfKeys > 0) { //if we have more than one key, then run though and create all params in the call...
  /* Here we look through and create the SP call string, with closing parens. */
  	  var query = "call "+SPName+"("+theKeys[0];
           var i= 1;
           for (i=1;i<numberOfKeys;i++)
              {var query = query+","+theKeys[i];}
  var query = query+")";
           }
  /* End creating the query string to be excecuted */
  Logger.log(query);        
  var start = new Date();
  Logger.log("Started fetching of data from database at:"+start);
  var rs = stmt.executeQuery(query);
  Logger.log("Data Fetching Completed at:"+new Date().toGMTString());
  Logger.log("Query time: " + (new Date().getTime() - start.getTime()));    
  var start = new Date();
  /* We get the JDBC metadata to set col headers automatically, and use correct
  getters.*/
  var meta = rs.getMetaData();
  var colcount = meta.getColumnCount();
  Logger.log("Number of Columns in recordset: "+colcount);
  // END DataBase SEtup
  //Get Column Headers Automatically from JDBC Definition
  var i = 0;
  var typeArray = new Array(); // an array to hold the datatypes of each column
  var labelArray = new Array(1); //an array to hold the lables, which later will be applied to the header.
  labelArray[0] = new Array(); // creates a second array inside of labelArray, since SetValues requires a 2 dimensional array.
  while(i < colcount) {
    typeArray.push(meta.getColumnTypeName(i+1)); //put columntypes into array for later use
    labelArray[0].push(meta.getColumnName(i+1)); //put columnnames into array so we can apply them to the header row.
    i++;
  };
// get the actual data, put in 2d dataArray 
  var row = 0;
  var dataArray  = new Array(); //create the main array
    while(rs.next()) {
   var col = 0;
   dataArray[row] = new Array(); //for each row in RS, create an array inside main array with values
        while (col < colcount) { // iterate through the columns
          switch(typeArray[col]){ // use the earlier created array with col metadata to use the correct JDBC getter to put the data into the data array.
              case "TIME": dataArray[row][col] = rs.getTime(col+1); break;
              case "TIMESTAMP": dataArray[row][col] = rs.getTimestamp(col+1); break;
              case "INT" : dataArray[row][col] = rs.getInt(col+1); break;
              case "DECIMAL" : dataArray[row][col] = rs.getBigDecimal(col+1); break;
              //case "FLOAT": dataArray[row][col] = rs.getFloat(col+1); break;
              case "REAL": dataArray[row][col] = rs.getFloat(col+1); break;
              case "TINYINT": dataArray[row][col] = rs.getInt(col+1); break;
              case "SMALLINT": dataArray[row][col] = rs.getInt(col+1); break;
            //  case "BIGINT": dataArray[row][col] = rs.getInt(col+1); break;
         //     case "DATE": dataArray[row][col] = rs.getDate(col+1); break;
              case "NUMERIC": dataArray[row][col] = rs.getBigDecimal(col+1); break;
              case "DOUBLE": dataArray[row][col] = rs.getDouble(col+1); break;
              default: dataArray[row][col] = rs.getString(col+1); //if it ain't those specified above, then just get it as a string.
          }
         col++;
       };
   row++; 
      } 
  //very important to close all connections
  rs.close(); 
  stmt.close();
  conn.close();
      return {typeArray : typeArray, labelArray : labelArray, dataArray : dataArray}; // return the three arrays in an object for later use.
}     

